using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
using DTO;

namespace DAOLayer
{
    public class LoaiSachDao
    {
        public static IList LayDanhSach()
        {
            ArrayList ds = new ArrayList();
            OleDbConnection cn;
            string strSQL;

            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = DataProvider.ConnectionData();

            // B3: Tao chuoi strSQL thao tac CSDL
            strSQL = "Select * From Loaisach";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                LoaiSachDto theloai = new LoaiSachDto();
                theloai.MALOAISACH = (int)dr["MALOAISACH"];
                theloai.LOAISACH = (string)dr["LOAISACH"];
                ds.Add(theloai);
            }
            // B5: Dong ket noi CSDL
            dr.Close();
            cn.Close();

            return ds;
        }

        public DataTable LayBang()
        {
            DataTable dt = new DataTable();
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();

            string strSQL;
            strSQL = "Select * From Loaisach";

            OleDbDataAdapter da;
            da = new OleDbDataAdapter(strSQL, cn);
            da.Fill(dt);

            cn.Close();
            return dt;
        }
        public static void Them(LoaiSachDto lsDto)
        {
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();

            string strSQL;
            strSQL = "Insert into Loaisach(Loaisach) values (?)" +
            "";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Loaisach", OleDbType.WChar);
            cmd.Parameters["@Loaisach"].Value = lsDto.LOAISACH;
          
            cmd.ExecuteNonQuery();
            strSQL = "Select @@IDENTITY";
            cmd = new OleDbCommand(strSQL, cn);
            lsDto.MALOAISACH = (int)cmd.ExecuteScalar();

            cn.Close();
        }
        public void Xoa(int maLoaiSach)
        {
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();

            string strSQL;
            strSQL = "Delete From Loaisach Where Maloaisach = ? ";

            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Maloaisach", OleDbType.Integer);
            cmd.Parameters["@Maloaisach"].Value = maLoaiSach;
            cmd.ExecuteNonQuery();

            cn.Close();
        }
        public void Sua(LoaiSachDto lsDto)
        {
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();

            string strSQL;
            strSQL = "Update Sach Set Loaisach = ? Where Maloaisach = ?";

            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Loaisach", OleDbType.WChar);
            cmd.Parameters.Add("@Maloaisach", OleDbType.Integer);
            cmd.Parameters["@Loaisach"].Value = lsDto.LOAISACH;
            cmd.Parameters["@Maloaisach"].Value = lsDto.MALOAISACH;

            cmd.ExecuteNonQuery();
            cn.Close();
        }
    }
}
